package cn.edu.lingnan.dao;

import cn.edu.lingnan.pojo.CourseDTO;
import cn.edu.lingnan.pojo.Teacher;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TeacherDaoImp implements TeacherDao {
    @Override
    public List<Teacher> pageQuery(Integer page, Integer pageSize) {
        List<Teacher> teacherList = new ArrayList<>();
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            String sql = "SELECT  tid, tname, age, gender, addres, phone, profession, academy FROM school.teacher  LIMIT ?, ?";
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setInt(1, (page - 1) * pageSize);
            ps.setInt(2, pageSize);
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
               Teacher teacher = new Teacher();
               teacher.setTid(rs.getInt("tid"));
               teacher.setTname(rs.getString("tname"));
               teacher.setAge(rs.getInt("age"));
               teacher.setGender(rs.getString("gender"));
               teacher.setAddress(rs.getString("addres"));
               teacher.setPhone(rs.getString("phone"));
               teacher.setProfession(rs.getString("profession"));
               teacher.setAcademy(rs.getString("academy"));
               teacherList.add(teacher);

            }
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return teacherList;    }

    @Override
    public Integer getTotalTeachers() {
        Integer  totalTeachers = 0;
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话
            PreparedStatement ps = conn.prepareStatement("SELECT COUNT(*) FROM school.teacher");

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                totalTeachers = rs.getInt(1);
            }

        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return  totalTeachers;
    }

    @Override
    public List<Teacher> getTeachersByName(String TeacherName) {
        List<Teacher> teacherList = new ArrayList<>();
        try{
            // 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立连接
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/school?useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true", "root", "123456");
            //建立会话

            PreparedStatement ps = conn.prepareStatement("SELECT * FROM school.teacher WHERE tname LIKE ?");

            ps.setString(1, "%" + TeacherName + "%");
            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                Teacher teacher = new Teacher();
                teacher.setTid(rs.getInt("tid"));
                teacher.setTname(rs.getString("tname"));
                teacher.setAge(rs.getInt("age"));
                teacher.setGender(rs.getString("gender"));
                teacher.setAddress(rs.getString("addres"));
                teacher.setPhone(rs.getString("phone"));
                teacher.setProfession(rs.getString("profession"));
                teacher.setAcademy(rs.getString("academy"));
                teacherList.add(teacher);
            }
        }
        catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        }

        return teacherList;
    }
}
